package com.yunshipei.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


import com.yunshipei.manager.YspPreferences;

import io.rong.app.utils.StringUtilSub;

/**
 * 数据库版本升级声明如下：
 * 数据库版本号必须为整数
 * 最早版本号为2
 */

public class EnterplorerHelper extends SQLiteOpenHelper {

    public static String DB_NAME = "com_yunshipei.db";             //数据库名称
    public static String TABLE_LOGIN = "login";                     //登陆表名
    public static String TABLE_USUAL_USER_LIST = "usuallist";
    public static String TABLE_DEPARTMENT = "department";
    public static String TABLE_GROUP = "ImGroup";
    public static String TABLE_NOTIFI = "notifi";
    public static String TABLE_COLLECTION = "collection";             //浏览器收藏
    public static int dbVersion = 7;                                //数据库版本
    private SQLiteDatabase dbutil;
    private static volatile EnterplorerHelper enterplorerHelper = null;

    //create table to save information about department
    public static String departmentInfo = "CREATE TABLE IF NOT EXISTS " + TABLE_DEPARTMENT + " (_id," + "departmentId primary key,departmentName,companyId,companyName,"
            + "parentDepartmentId,memberNumber,createdTime,updatedTime)";
    //login 登陆表
    public static String login = "CREATE TABLE IF NOT EXISTS " + TABLE_LOGIN + "(_id PRIMARY KEY,userId,name,userName,telephone,email,password,departmentId,companyId,company,createdTime,updatedTime);";
    /**
     * 数据库从2升级到3
     */
    public static String groupInfoString = "create table if not exists " + TABLE_GROUP + " (id primary key,name,portraitUri,introduce,"
            + "number,max_number,create_user_id,create_dateTime,groupMembers);";
    private static String CREATE_TMEP_GROUP = "alter table " + TABLE_GROUP + " rename  to temp_ImGroup";
    private static String INSERT_DATA = "insert into " + TABLE_GROUP + " select id,name,portraitUri,introduce,number,max_number,create_user_id,create_dateTime,'' from temp_ImGroup";
    private static String DROP_GROUP = "drop table temp_ImGroup";
    /**
     * 数据库从3升级到4
     */
    private static String TEMP_USER_LIST = "alter table " + TABLE_USUAL_USER_LIST + " rename to temp_userList";
    public static String USERLIST = "create table if not exists " + TABLE_USUAL_USER_LIST + " ("
            + "uuid primary key,name,password,department,departmentId,companyId,position,tel,email,createdTime,avatar,isAdmin);";
    private static String INSERT_DATE_USERLIST = "insert into " + TABLE_USUAL_USER_LIST + " select uuid,name,password,department,departmentId,companyId,position,tel,email,createdTime,avatar,'false' from temp_userList";
    private static String DROP_TEMP_USERLIST = "drop table temp_userList";
    /**
     * 数据库从4升级到5
     * 添加收藏表
     */
    private String collection = "CREATE TABLE IF NOT EXISTS " + TABLE_COLLECTION + "(_id INTEGER PRIMARY KEY autoincrement,url,title,imageUrl,uuid);";
    private String collection1 = "CREATE TABLE IF NOT EXISTS " + TABLE_COLLECTION + "(_id INTEGER PRIMARY KEY autoincrement,url,title,imageUrl);";
    /**
     * 数据库从5升级到6
     * 添加收藏表
     */
    private String updateCollection = "ALTER TABLE " + TABLE_COLLECTION + " ADD COLUMN uuid BLOB";
    private String defaultCollectionUUID = "update " + TABLE_COLLECTION + " set uuid=" + "\'" + YspPreferences.getInstance().getSharedPreferences
            ().getString("uuid", "") + "\'" + " where uuid  IS NULL";
    /**
     * 数据库从6升级到7
     * 删除收藏表
     */
    private String DROP_COLLECTION_TABLE = "drop table if exists " + TABLE_COLLECTION;

    /**
     * super(参数1，参数2，参数3，参数4)，其中参数4是代表数据库的版本，
     * 是一个大于等于1的整数，如果要修改（添加字段）表中的字段，则设置
     * 一个比当前的 参数4大的整数 ，把更新的语句写在onUpgrade(),下一次
     * 调用
     */
    public EnterplorerHelper(Context context) {
        super(context, DB_NAME, null, dbVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        dbutil = db;
        db.execSQL(login);              //创建表
        db.execSQL(departmentInfo);     //部门表
        db.execSQL(groupInfoString);    //创建群组
        db.execSQL(USERLIST);           //users表
        db.execSQL(collection);         //URL收藏表
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d("com_yunshipei.db", "升级");
        // TODO Auto-generated method stub
        oldVersion += 1;
        for (; oldVersion <= newVersion; oldVersion++) {
            if (oldVersion == 3) {
                db.execSQL(CREATE_TMEP_GROUP);
                db.execSQL(groupInfoString);//创建群组
                db.execSQL(INSERT_DATA);
                db.execSQL(DROP_GROUP);
                Log.d("数据库版本", "2 ----> 3");
            }

            if (oldVersion == 4) {
                db.execSQL(TEMP_USER_LIST);
                db.execSQL(USERLIST);
                db.execSQL(INSERT_DATE_USERLIST);
                db.execSQL(DROP_TEMP_USERLIST);
                Log.d("数据库版本", "3 ----> 4");
            }

            if (oldVersion == 5) {
                db.execSQL(login);              //创建表
                db.execSQL(departmentInfo);     //部门表
                db.execSQL(groupInfoString);    //创建群组
                db.execSQL(USERLIST);           //users表
                db.execSQL(collection1);         //URL收藏表
                Log.d("数据库版本", "4 ----> 5");
            }

            if (oldVersion == 6) {
                db.execSQL(updateCollection);
                db.execSQL(defaultCollectionUUID);
                Log.d("数据库版本", "5 ----> 6");
            }

            if (oldVersion == 7) {
                db.execSQL(DROP_COLLECTION_TABLE);
                Log.d("数据库版本", "6 ----> 7");
            }

        }
    }


    /**
     * rongCloud数据库
     *
     * @param dataPath
     * @return
     */
    public SQLiteDatabase getRongCloudDatabase(String dataPath) {
        SQLiteDatabase sqLiteDatabase = null;
        sqLiteDatabase = SQLiteDatabase.openDatabase(dataPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        return sqLiteDatabase;
    }

    /**
     * 获取融云数据库
     *
     * @param context
     * @return
     */
    public SQLiteDatabase getRongCloudDatabase(Context context) {

        return getRongCloudDatabase(StringUtilSub.getRongCloudDatabasePath(context));
    }

    public String getUserListTableSql() {
        return USERLIST;
    }

}
